package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Customer;
import ModelLayer.Delivery;
import ModelLayer.Location;
import ModelLayer.SalesLine;

public class DbDelivery implements IFDBDelivery{
	
	private Connection con;
	
	public DbDelivery(){
		con = DbConnection.getInstance().getDBcon();
	}
	
	@Override
	public ArrayList<Delivery> getAllDelivery() {
		return miscWhere("");
	}
	
	@Override
	public Delivery getDeliveryById(int id) {
		String wClause = "id = '" + id + "'";
		System.out.println("Search location " + wClause);
		return singleWhere(wClause);
	}
	
	private ArrayList<Delivery> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Delivery> list = new ArrayList<Delivery>();	

		String query =  buildQuery(wClause);

		try{ // read the salesLine from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while(results.next()){
				Delivery delObj = new Delivery();
				delObj = buildDelivery(results);	
				list.add(delObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	//Singlewhere to select a city 	
		private Delivery singleWhere(String wClause)
		{
			ResultSet results;
			Delivery deliveryObj = new Delivery();
			
		
				String query =  buildQuery(wClause);
				System.out.println(query);
				try{ // read the delivery from the database
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					results = stmt.executeQuery(query);

					if( results.next() ){
						deliveryObj = buildDelivery(results);
						stmt.close();
					}		
					else{ //no delivery was found
						deliveryObj = null;
					}
				}
				catch(Exception e){
					System.out.println("Query exception: "+e);
				}

			return deliveryObj;
		}
		
		//method to build the query for Customer
		private String buildQuery(String wClause)
		{
			String query="SELECT id, deliveryCost, deliveryState, deliveryDate FROM Delivery";

			if (wClause.length()>0)
				query=query+" WHERE "+ wClause;

			return query;
		}
		
		//method to build a PrivateCustomer object
		private Delivery buildDelivery(ResultSet results)
		{   
			Delivery deliveryObj = new Delivery();
			try{ // the columns from the table customer  are used
				deliveryObj.setDeliveryId(results.getInt("id"));
				deliveryObj.setDeliveryCost(results.getDouble("deliveryCost"));
				deliveryObj.setDeliveryState(results.getString("deliveryState"));
				deliveryObj.setDeliveryDate(results.getDate("deliveryDate"));
			}
			catch(Exception e)
			{
				System.out.println("error in building the city object");
			}
			return deliveryObj;
		}
}
